package cn.edu.tsinghua.weibo.action;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.edu.tsinghua.weibo.util.DB;

public class JDBC_MySQL_Function {
	
	private final static String SQL = "SELECT * FROM weibo WHERE FIND_IN_SET(id, getParentList(?)) ORDER BY time DESC";
	public static void main(String[] args) {
		Connection connection = DB.getConnection();
		try {
	        CallableStatement callableStatement = connection.prepareCall(SQL);
	        callableStatement.setInt(1, );
			ResultSet resultSet = callableStatement.executeQuery();
			while(resultSet.next()) {
				System.out.println(resultSet.getInt("id") + ", " + resultSet.getString("content") + ", " + resultSet.getInt("forwardid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
/*
	DELIMITER $$  
	
	DROP FUNCTION IF EXISTS `weibo`.`getParentList`$$  
	  
	CREATE DEFINER=`root`@`localhost` FUNCTION `getParentList`(childId VARCHAR(50)) RETURNS VARCHAR(1000) CHARSET utf8  
		BEGIN  
			DECLARE sTemp VARCHAR(1000);  
			DECLARE sTempParent VARCHAR(1000);  
	
			SET sTemp = '$';  
			SET sTempParent =childId;
		       
			WHILE sTempParent IS NOT NULL DO  
				SET sTemp = CONCAT(sTemp,',',sTempParent);  
				SELECT GROUP_CONCAT(forwardid) INTO sTempParent FROM weibo WHERE forwardid<>id AND FIND_IN_SET(id,sTempParent)>0;  
			END WHILE;  
			RETURN sTemp;  
		END$$  
	  
	DELIMITER ;
*/